package com.five.wisdomClass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.five.wisdomClass.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

@Mapper
public interface TeacherMapper  extends BaseMapper<Teacher> {


    // 获取教师对象
    @Select("SELECT * FROM smartcampus.teacher_info WHERE teacher_number = #{teacherNumber}")
    Teacher getTeacher(String teacherNumber);

    // 通过教师编号获取公共课程信息
    @Select("SELECT course_number,course,start_time,end_time,location FROM smartcampus.public_course WHERE teacher_number = #{teacherNumber} AND state = 1")
    List<Course> getPublicCourse(@Param("teacherNumber") String teacherNumber);

    // 通过教师编号获取选修课程信息
    @Select("SELECT course_number,course,start_time,end_time,location FROM smartcampus.take_course WHERE teacher_number = #{teacherNumber} AND state = 1")
    List<Course> getTakeCourse(@Param("teacherNumber") String teacherNumber);

    // 查询所在课程中的所有学生
    // 通过课程号在关系表中查询班级号
    @Select("SELECT class_id FROM smartcampus.class_public_course WHERE course_number = #{courseNumber}")
    String getClassId(@Param("courseNumber") String courseNumber);
    // 通过班级号在班级表中查询班级名称
    @Select("SELECT class AS className FROM smartcampus.specialized_class WHERE class_id = #{classId}")
    String getClassName(@Param("classId") String classId);
    // 通过班级名称在学生信息表中查询学生集合
    @Select("SELECT s_number,s_name,s_phone,major FROM smartcampus.studentinfo WHERE class = #{className}")
    IPage<Student> selectStudents(Page<?> page,String className);

    // 根据课程号在关系表中获取学生学号集合
    @Select("SELECT s_number FROM smartcampus.student_take_course WHERE course_number = #{courseNumber}")
    List<String> getStudentNumbers(@Param("courseNumber") String courseNumber);

    // 根据学号查询信息表中的学生
    @Select("SELECT s_number,s_name,s_phone,major,class AS className FROM smartcampus.studentinfo WHERE s_number = #{sNumber}")
    Student selectStudent(@Param("sNumber") String sNumber);

    // 查询课程中请假的学生
    @Select("SELECT * FROM smartcampus.leave WHERE s_number = #{sNumber} ORDER BY start_time DESC")
    List<Leave> getCourseStudentLeave(@Param("sNumber") String sNumber);

    // 通过班级名称获取学生学号集合
    @Select("SELECT s_number FROM smartcampus.studentinfo WHERE class = #{className}")
    List<String> getClassStudentsNumber(String className);
    /**
     * 查看请假列表
     */
    // 通过教师编号去公共课程表中获取课程号列表
    @Select("SELECT course_number FROM smartcampus.public_course WHERE teacher_number = #{teacherNumber}")
    List<String> getPublicCourseNumbers(@Param("teacherNumber") String teacherNumber);
    // 通过课程号去关系表中获取对应的班级号列表(已完成)
    // 查询班级名称(已完成)
    // 通过班级名列表获取所有班级的学生的学号集合
    @Select("SELECT s_number FROM  smartcampus.studentinfo WHERE class = #{className}")
    List<String> getStudentNumbersByClassName(@Param("className") String className);
    // 通过学号查询请假申请
    // 通过教师编号去选修课程表中查询课程号列表
    @Select("SELECT course_number FROM smartcampus.take_course WHERE teacher_number = #{teacherNumber}")
    List<String> getTakeCourseNumbers(@Param("teacherNumber") String teacherNumber);
    // 通过课程号去查询关系表中的学号集合(已完成)
    // 通过学号查询请假申请
    @Select("SELECT id,s_number,s_name,leave_type,start_time,end_time,contact,context,state,reason FROM smartcampus.leave WHERE s_number = #{sNumber}")
    List<Leave> getStudentLeaves(@Param("sNumber") String sNumber);


    /**
     *查看课程人数
     */
    // 通过班级名称在学生信息表中查询学号集合
    @Select("SELECT s_number FROM smartcampus.studentinfo WHERE class = #{className}")
    List<String> getClassStudentNumbers(String className);

    /**
     * 签到
     */
   // 发布签到
    @Insert("INSERT INTO smartcampus.sign_info(course_number,teacher_name,s_number,s_name,post_time,code) VALUES(#{courseNumber},#{teacherName},#{sNumber},#{sName},current_time,#{code})")
    int postSignIn(SignInfo signInfo);

    // 通过教师编号获取公共课程
    @Select("SELECT distinct course_number,course,teacher_name FROM smartcampus.public_course WHERE teacher_number = #{teacherNumber}")
    List<Course> getTeachPublicCourse(@Param("teacherNumber") String teacherNumber);

    // 通过教师编号获取选修课程
    @Select("SELECT distinct course_number,course,teacher_name FROM smartcampus.take_course WHERE teacher_number = #{teacherNumber}")
    List<Course> getTeachTakeCourse(@Param("teacherNumber") String teacherNumber);

//    // 查看发布的签到列表
    @Select("SELECT s.* FROM smartcampus.sign_info s " +
            "INNER JOIN ( " +
            "    SELECT MIN(id) as min_id, post_time " +
            "    FROM smartcampus.sign_info " +
            "    WHERE course_number = #{courseNumber} " +
            "    GROUP BY post_time " +
            ") sub ON s.id = sub.min_id AND s.post_time = sub.post_time " +
            "WHERE s.course_number = #{courseNumber} ORDER BY post_time DESC")
    IPage<SignInfo> getSignInfos(Page<?> page, String courseNumber);
    // 查看某课程发布的已签到学生，根据该签到发布的时间
    @Select("SELECT id,s_number,s_name,post_time,actual_time,sign_state FROM smartcampus.sign_info WHERE sign_state = 1 AND post_time = #{postTime}")
    IPage<SignInfo> getSignStudents(Page<?> page, String postTime);

    // 查看某课程发布的未签到学生，根据该签到发布的时间
    @Select("SELECT id,s_number,s_name,post_time,actual_time,sign_state FROM smartcampus.sign_info WHERE sign_state IN (0, 3) AND post_time = #{postTime}")
    IPage<SignInfo> getUnSignStudents(Page<?> page, String postTime);


    /**
     * 排课
     */
    // 检查公共课程冲突
    @Select("SELECT COUNT(*) FROM smartcampus.public_course WHERE location = #{location} " +
            "AND state = 1 " +
            "AND ((start_time < #{end_time} AND end_time > #{start_time}) " +
            "OR (start_time < #{start_time} AND end_time > #{end_time}) " +
            "OR (start_time >= #{start_time} AND start_time < #{end_time}) " +
            "OR (end_time > #{start_time} AND end_time <= #{end_time}))")
    int checkPublicCourse(@Param("location") String location, @Param("start_time") String startTime, @Param("end_time") String endTime);

    // 检查选修课程冲突
    @Select("SELECT COUNT(*) FROM smartcampus.take_course WHERE location = #{location} " +
            "AND state = 1 " +
            "AND ((start_time < #{end_time} AND end_time > #{start_time}) " +
            "OR (start_time < #{start_time} AND end_time > #{end_time}) " +
            "OR (start_time >= #{start_time} AND start_time < #{end_time}) " +
            "OR (end_time > #{start_time} AND end_time <= #{end_time}))")
    int checkTakeCourse(@Param("location") String location, @Param("start_time") String startTime, @Param("end_time") String endTime);


    // 插入公共课
    @Insert("INSERT INTO smartcampus.public_course (course_number, course,teacher_name, start_time, end_time, location, teacher_number) " +
            "VALUES (#{courseNumber}, #{course}, #{teacherName}, #{startTime}, #{endTime}, #{location}, #{teacherNumber})")
    int insertPublicCourse(Course course);

    // 插入选修课
    @Insert("INSERT INTO smartcampus.take_course (course_number, course,teacher_name, start_time, end_time, location, teacher_number) " +
            "VALUES (#{courseNumber}, #{course}, #{teacherName}, #{startTime}, #{endTime}, #{location}, #{teacherNumber})")
    int insertTakeCourse(Course course);

    // 判断是公共课
    @Select("SELECT COUNT(course_number) FROM smartcampus.class_public_course WHERE course_number = #{courseNumber}")
    int checkPublic(String courseNumber);

    // 判断是选修课
    @Select("SELECT COUNT(course_number) FROM smartcampus.student_take_course WHERE course_number = #{courseNumber}")
    int checkTake(String courseNumber);


    // 根据课程号获取这门课的课程
    // 选修课
    @Select("SELECT * FROM smartcampus.take_course WHERE course_number = #{courseNumber}")
    IPage<Course> getOneTakeCourse(Page<?> page,String courseNumber);
    // 公共课
    @Select("SELECT * FROM smartcampus.public_course WHERE course_number = #{courseNumber}")
    IPage<Course> getOnePublicCourse(Page<?> page,String courseNumber);

    // 修改某堂公共课的时间或者地点
    @Update("<script>" +
            "update smartcampus.public_course " +
            "<set> " +
            "    <if test=\"startTime != null\"> " +
            "        start_time = #{startTime}, " +
            "    </if> " +
            "    <if test=\"endTime != null\"> " +
            "        end_time = #{endTime}, " +
            "    </if> " +
            "    <if test=\"location != null\"> " +
            "        location = #{location}, " +
            "    </if> " +
            "    state = 3 " +
            "</set> " +
            "where course_number = #{courseNumber} and id = #{id}" +
            "</script>")
    int updatePublicCourse(Course course);

    // 修改某堂选修课的时间或者地点
    @Update("<script>" +
            "update smartcampus.take_course " +
            "<set> " +
            "    <if test=\"startTime != null\"> " +
            "        start_time = #{startTime}, " +
            "    </if> " +
            "    <if test=\"endTime != null\"> " +
            "        end_time = #{endTime}, " +
            "    </if> " +
            "    <if test=\"location != null\"> " +
            "        location = #{location}, " +
            "    </if> " +
            "    state = 3 " +
            "</set> " +
            "where course_number = #{courseNumber} and id = #{id}" +
            "</script>")
    int updateTakeCourse(Course course);
}


